package cn.edu.seu.herald.informationplatform.dao.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import cn.edu.seu.herald.informationplatform.bean.league.LeagueUser;

import com.mysql.jdbc.Statement;

public class LeagueUserDao extends DataAccessObject{
	private static LeagueUserDao daoInstance=null;
	
	//Constructor of LeagueDao
	protected LeagueUserDao(){
		
	}
	
	//judge have id
	public boolean hasId(int id) throws SQLException{
		Connection con=getConnectionLeague();
		try {
			PreparedStatement ps = con.prepareStatement("Select * from lg_league where id=?");
			ps.setInt(1,id);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				closeRs(rs);
				return true;
			}
			
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
			
		}
	}
	//Get LeagueDao instance
	public static LeagueUserDao getDao(){
		if(daoInstance==null){
			daoInstance=new LeagueUserDao();
		}
		return daoInstance;
	}
	
	//add 
	//Return:the new item's id
	public int addLeagueUser(LeagueUser newLeagueUser) throws SQLException{
		Connection con=getConnectionLeague();
		try {
			PreparedStatement ps=con.prepareStatement("insert into lg_league(username,password,lg_league_info_id) values(?,?,?)",Statement.RETURN_GENERATED_KEYS);
			ps.setString(1,newLeagueUser.getUsername());
			ps.setString(2,newLeagueUser.getPassword());
			ps.setInt(3,newLeagueUser.getLeagueInfoId());
			ps.execute();
			ResultSet rsResultSet=ps.getGeneratedKeys();
			int new_id=-1;
			if(rsResultSet.next()){
				new_id=rsResultSet.getInt(1);
			}
			closeRs(rsResultSet);
			return new_id;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}	
	}
	
	//delete
	public void deleteLeagueUser(int id) throws SQLException{
		Connection con=getConnectionLeague();
		try {
				PreparedStatement ps=con.prepareStatement("delete from lg_league where id=?");
				ps.setInt(1,id);
				ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
	
	//update
	public void updateLeagueUser(int id,LeagueUser newLeagueUser) throws SQLException{
		Connection con=getConnectionLeague();
		try {
				PreparedStatement ps=con.prepareStatement("update lg_league set username=?,password=? where id=?");
				ps.setString(1,newLeagueUser.getUsername());
				ps.setString(2,newLeagueUser.getPassword());
				ps.setInt(3,id);
				ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
	
	//get
	//Return:object of LeagueUser
	public LeagueUser getLeagueUser(int id) throws SQLException{
		Connection con=getConnectionLeague();
		try {
				LeagueUser leagueUser=new LeagueUser();
				PreparedStatement ps=con.prepareStatement("select username,password,lg_league_info_id from lg_league where id=?");
				ps.setInt(1,id);
				ResultSet rsResultSet=ps.executeQuery();
				while(rsResultSet.next()){
					leagueUser.setUsername(rsResultSet.getString(1));
					leagueUser.setPassword(rsResultSet.getString(2));
					leagueUser.setLeagueInfoId(rsResultSet.getInt(3));
				}
				closeRs(rsResultSet);
				return leagueUser;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
}
